package com.test.sqlformat;
import net.sf.jsqlparser.parser.CCJSqlParserUtil;
import net.sf.jsqlparser.statement.Statement;
import net.sf.jsqlparser.util.deparser.StatementDeParser;

public class SQLFormatter {

    public static String formatSQL(String sql) {
        try {
            // 解析SQL语句
            Statement statement = CCJSqlParserUtil.parse(sql);
            // 创建StringBuilder来存储格式化后的SQL
            StringBuilder builder = new StringBuilder();
            // 创建StatementDeParser来格式化SQL
            StatementDeParser deparser = new StatementDeParser(builder);
            statement.accept(deparser);
            // 返回格式化后的SQL
            final String string = builder.toString();
            System.out.println(string + "\n");
            return beautifySQL(string);
        } catch (Exception e) {
            e.printStackTrace();
            return sql;  // 如果解析或格式化失败，返回原始SQL
        }
    }
    private static String beautifySQL(String sql) {
        // 正则表达式替换，增加换行和缩进
        sql = sql.replaceAll("\\s+FROM\\s+", "\nFROM ")
            .replaceAll("\\s+WHERE\\s+", "\nWHERE ")
            .replaceAll("\\s+AND\\s+", "\n  AND ")
            .replaceAll("\\s+OR\\s+", "\n  OR ")
            .replaceAll("\\s+ORDER BY\\s+", "\nORDER BY ")
            .replaceAll("\\s+GROUP BY\\s+", "\nGROUP BY ")
            .replaceAll("\\s+HAVING\\s+", "\nHAVING ")
            .replaceAll("\\s+INNER JOIN\\s+", "\nINNER JOIN ")
            .replaceAll("\\s+LEFT JOIN\\s+", "\nLEFT JOIN ")
            .replaceAll("\\s+RIGHT JOIN\\s+", "\nRIGHT JOIN ")
            .replaceAll("\\s+FULL JOIN\\s+", "\nFULL JOIN ")
            .replaceAll("\\s+JOIN\\s+", "\nJOIN ");

        // 添加缩进和格式化
        String[] lines = sql.split("\n");
        StringBuilder formattedSQL = new StringBuilder();
        for (String line : lines) {
            formattedSQL.append(line.trim()).append("\n");
        }
        return formattedSQL.toString().trim();
    }

    public static void main(String[] args) {
        String sql = "    SELECT c.dn, b.*, c.countyflag, c.countyname\n" +
                     "    from uom_view_usercompositor a left join uom_view_userinfo b on a.USERID = b.USERID left join uom_view_departmentinfo c on a.orgid = c.id where c.dn = 'OU=huzmcc,O=zmcc'  and b.PERSONTYPE = 'R'\n" +
                     "      and b.STATUS = 1\n" +
                     "      and a.DOMAIN = 'ZMCC'\n" +
                     "      and b.DOMAIN = 'ZMCC' order by a.COMPOSITOR;";
        String formattedSQL = formatSQL(sql);
        System.out.println("Formatted SQL: \n" + formattedSQL);
    }
}
